import sys
from pathlib import Path

file = Path(__file__).resolve()
parent, top = file.parent, file.parents[2]

sys.path.append(str(top))
try:
    sys.path.remove(str(parent))
except ValueError: # Already removed
    pass

__package__ = 'src.utils'

from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker
from ..config import username, password, url, port, db, oracle_host, oracle_password, oracle_port, oracle_service_name, oracle_username, mes_db, sql_host, sql_db, sql_password, sql_username, sqle_port
import oracledb
import pymssql
from contextlib import contextmanager


# MySQL 数据库访问地址
MySQL_DATABASE_URL = f"mysql+pymysql://{username}:{password}@{url}:{port}/{db}?charset=utf8"
#启动引擎
engine = create_engine(
    MySQL_DATABASE_URL,
    pool_recycle=3600
)
#启动会话
SessionLocal = sessionmaker(bind=engine)


#数据模型的基类
Base = declarative_base()


# MES 数据库访问地址
MES_DATABASE_URL = f"mysql+pymysql://{username}:{password}@{url}:{port}/{mes_db}?charset=utf8"
#启动引擎
mes_engine = create_engine(
    MES_DATABASE_URL,
    pool_recycle=3600
)
#启动会话
MES_Session = sessionmaker(bind=mes_engine)


# 配置 Oracle 数据库连接信息
Oracle_DATABASE_URL = f"oracle+oracledb://{oracle_username}:{oracle_password}@{oracle_host}:{oracle_port}/?service_name={oracle_service_name}"
oracle_engine = create_engine(
    Oracle_DATABASE_URL
)
dsn = oracledb.makedsn(oracle_host, oracle_port, service_name=oracle_service_name)


@contextmanager
def connect_oracle():
    conn = None
    try:
        conn = oracledb.connect(user=oracle_username, password=oracle_password, dsn=dsn)
        print("Oracle connection established.")
        yield conn
    except pymssql.Error as e:
        print(f"Oracle connection failed: {e}")
        raise
    finally:
        if conn:
            conn.close()
            print("Oracle connection closed.")

@contextmanager
def connect_sqlserver():

    conn = None

    try:
        conn = pymssql.connect(sql_host, sql_username, sql_password, sql_db)
        print("SqlServer connection established.")
        yield conn
    except pymssql.Error as e:
        print(f"SqlServer connection failed: {e}")
        raise
    finally:
        if conn:
            conn.close()
            print("SqlServer connection closed.")



   
    

if __name__ == "__main__":
    # connection = connect_oracle()

    # 查询视图数据
    # query_sql = """SELECT CHIP_ID,  NG_CODE, START_DATE_TIME, WORKER_ID 
    #                     FROM (SELECT CHIP_ID,  NG_CODE, START_DATE_TIME, WORKER_ID,
    #                     ROW_NUMBER() OVER (PARTITION BY CHIP_ID ORDER BY START_DATE_TIME DESC) AS rn
    #                 FROM V_R_JR_CHIP_PANEL_ISP_AI
    #                 WHERE START_DATE_TIME > TO_DATE('2025-04-15 09:00:21', 'YYYY-MM-DD HH24:MI:SS ') and WORKER_ID !='SYSTEM' and CHIP_ID='GR5V2297SDD'
    #             ) subquery
    #             WHERE rn = 1"""


    query_sql = """SELECT CHIP_ID,  NG_CODE, START_DATE_TIME, WORKER_ID 
                        FROM (SELECT CHIP_ID,  NG_CODE, START_DATE_TIME, WORKER_ID,
                        ROW_NUMBER() OVER (PARTITION BY CHIP_ID ORDER BY START_DATE_TIME DESC) AS rn
                    FROM V_R_JR_CHIP_PANEL_ISP_AI
                    WHERE START_DATE_TIME > TO_DATE('2025-05-9 09:00:00', 'YYYY-MM-DD HH24:MI:SS ') and WORKER_ID !='SYSTEM'
                ) subquery
                WHERE rn = 1"""

    # # 获取字段名
    # column_names = [col[0] for col in oracle_cursor.description]
    # print(column_names)

    # rows = oracle_cursor.fetchall()

    # # print(rows[0])

    # # print(len(rows))

    # # print("rows: ", rows)

    # # 格式化结果
    # for row in rows:
    #     print(row)

    # # 关闭游标和连接
    # oracle_cursor.close()
    # connection.close()

    chip_id = "N]5D4375QA2"
    aoi_time = None


    # query = f"SELECT ChipID, LotNo, EquipNo, DetectionTime FROM your_table_name Where ChipID='{chip_id}' and DetectionTime='{aoi_time}'"
    query = f"SELECT ChipID, LotNo, EquipNo, DetectionTime FROM DetectHistory Where ChipID='{chip_id}'"
    query = f"SELECT ChipID, LotNo, EquipNo, DetectionTime FROM DetectHistory Where DetectionTime>'2025-05-22'"

    try:
        with connect_sqlserver() as sql_con:
            with sql_con.cursor() as sql_cursor:
                sql_cursor.execute(query)

                # 获取结果
                rows = sql_cursor.fetchall()
                for row in rows:
                    print(row)
    except Exception:
        print("SqlServer 数据库连接失败")
